﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Flow_GetMyDocument_V2]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Flow_GetMyDocument_V2];
GO
CREATE PROCEDURE [dbo].[sproc_Flow_GetMyDocument_V2]
    @StaffName nvarchar(300)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @staffid int

SELECT @staffid = staff_id FROM dbo.UDS_Staff WHERE staff_name = @StaffName;
IF @@rowcount <> 1
BEGIN
	raiserror(N'The specified user "%s" cannot be found.', 16, 1, @StaffName);
    return -1;
END;

SELECT
        doc.Doc_ID,
        doc.Doc_Builder_ID,
        doc.Doc_Added_Date,
        doc.Doc_Status,
        doc.Flow_ID,
        doc.Step_ID,
        data.A as [Title],
        staff.Staff_ID,
        staff.Staff_Name,
        staff.Realname,
        flow.Flow_Name,
        step.Step_Name
    FROM 
        dbo.uds_flow_Style_Data data,
        dbo.uds_flow_document doc,
        dbo.uds_staff staff,
        dbo.uds_flow flow,
        dbo.uds_flow_step step
    WHERE
        data.Doc_ID             = doc.Doc_ID 
        and doc.flow_id         = flow.flow_id
        and doc.flow_id         = step.flow_id
        and doc.step_id         = step.step_id
        and doc.doc_builder_id  = staff.staff_id
        and staff.staff_id      = @staffid    
        and doc.IsRunning = 1
    ORDER BY
        doc.doc_added_date DESC

END
GO
